<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Report_Model extends CI_Model {
	
	public function generate_recievers_report($year) {
		$query = "SELECT ssu.id, ssu.name AS student, ssu.number, sm.major, ss.name, ss.period_start, 
			ss.period_end, ss.details
			FROM smb_scholarships AS ss JOIN smb_scholarship_applicants AS ssa
			ON ss.id = ssa.id JOIN smb_students AS ssu ON ssa.username = ssu.username
			JOIN smb_majors AS sm ON ssu.major_id = sm.id
			WHERE (YEAR(ss.period_start) = ? OR YEAR(ss.period_end) = ?) 
			AND ssa.status IN ('aktif', 'non-aktif') ORDER BY ss.name ASC, ssu.number ASC";
		$result = $this->db->query($query, array($year, $year));
		if ($result->num_rows() > 0) {
			return $result->result_array();
		} else {
			return NULL;
		}
	}
	
	public function generate_current_registrants() {
		$query = "SELECT ssu.id, ssu.name AS student, ssu.number, sm.major, ss.name, ss.details,
			IFNULL((SELECT GROUP_CONCAT(ass.name ORDER BY ass.name SEPARATOR ', ') 
			FROM smb_scholarships AS ass JOIN smb_scholarship_applicants AS assa ON
			ass.id = assa.id WHERE assa.status IN ('aktif', 'non-aktif') 
			AND assa.username = ssu.username GROUP BY assa.username), '-') AS accepted
			FROM smb_scholarships AS ss JOIN smb_scholarship_applicants AS ssa
			ON ss.id = ssa.id JOIN smb_students AS ssu ON ssa.username = ssu.username
			JOIN smb_majors AS sm ON ssu.major_id = sm.id 
			WHERE ss.deadline > CURRENT_DATE 
			AND ssa.status IN ('terverifikasi') ORDER BY ss.name ASC, ssu.number ASC";
		$result = $this->db->query($query);
		if ($result->num_rows() > 0) {
			return $result->result_array();
		} else {
			return NULL;
		}
	}
	
	public function generate_registrants_report($year) {
		$query = "SELECT ssu.id, ssu.name AS student, ssu.number, sm.major,
			IFNULL(GROUP_CONCAT(ss.name ORDER BY ss.name SEPARATOR ', '), '-') AS registered
			FROM smb_scholarships AS ss JOIN smb_scholarship_applicants AS ssa
			ON ss.id = ssa.id JOIN smb_students AS ssu ON ssa.username = ssu.username
			JOIN smb_majors AS sm ON ssu.major_id = sm.id
			WHERE (YEAR(ss.period_start) = ? OR YEAR(ss.period_end) = ?)
			AND ssa.status IN ('terverifikasi', 'ditolak', 'direkomendasikan')
			AND ssu.username NOT IN (SELECT ussa.username FROM smb_scholarships AS uss 
			JOIN smb_scholarship_applicants AS ussa ON uss.id = ussa.id
			WHERE (YEAR(uss.period_start) = ? OR YEAR(uss.period_end) = ?) AND ussa.status
			IN ('aktif', 'non-aktif'))
			GROUP BY ssu.id, ssu.name, ssu.number, sm.major
			ORDER BY ssu.number ASC";
		$result = $this->db->query($query, array($year, $year, $year, $year));
		if ($result->num_rows() > 0) {
			return $result->result_array();
		} else {
			return NULL;
		}
	}
	
}